package cn.com.callback

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.com.elite.EliteBean;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import org.apache.commons.lang.StringUtils;

import cn.com.elite.Util;

/**
 *
 * @Title: aipHSTForCustomerMergeEsop
 * @Description:华盛证券 Esop 客户合并 由多个副客户合并到一个主客户 --内部接口
 * @author: chieh
 * @date 2023-10-13 14:30:00
 *
 */

public class aipHSTForCustomerMergeEsop implements ServiceBackInvoke{
	private static final Log log=LogFactory.getLog(aipHSTForCustomerMergeEsop.class);
	public String doCallBack(EliteBean eliteBean,HttpServletRequest request,HttpServletResponse response){

 // P_MergeCust(IN `MainCustGUID` varchar(36),IN `MergeCustList` varchar(370),IN `MergePattern` int,IN `Operater` varchar(6),OUT o_Result int)
 		//接受传参 主客户guid
		    def mainCustGuid = request.getParameter("MainCustGUID");
		    log.debug("mainCustGuid:"+mainCustGuid);
		//接受传参 合并客户，guid以逗号相隔    
		    def mergeCustList = request.getParameter("MergeCustList");
		     log.debug("mergeCustList:"+mergeCustList);
		//mergeCustForSql把合并客户转化为‘A,'B’....sql的in认识的方式   
			def mergeCustForSql = mergeCustList.replaceAll(",","','"); 			
		     mergeCustForSql = "'" + mergeCustForSql + "'"; 
		     log.debug("mergeCustForSql:"+mergeCustForSql);
		//操作人    
		    def operater = StringUtils.trimToEmpty(request.getParameter("Operater"));
		     log.debug("operater:"+operater);
		//接受传参 合并模式，先支持普通合并=1，以后扩展补充模式=2····    
		    def mergePattern = request.getParameter("MergePattern");
		    if(!"2".equals(mergePattern)){
		    	mergePattern = "1";
		    }
		     log.debug("mergePattern:"+mergePattern);
					
			List<String> nullFieldList = new ArrayList<String>();

			
			def now="";
			def sqlType="";
			def custsqlfield="CUSTOMER_GUID,CUSTOMER_ID,IDTYPE,CUSTOMERNAME,CUSTOMERID,CUSTOMERTYPE_ID,CUSTOMERID2,CUSTOMERTYPE2_ID,PROVINCE_ID,CITY_ID,ADDRESS,BIRTHDAY,GENDER,VIP,TEL_1,TEL_2,FAX,E_MAIL,MOBILE,OTHERS,STRINGFIELD1,STRINGFIELD2,STRINGFIELD3,STRINGFIELD4,STRINGFIELD5,STRINGFIELD6,CREATEDBY,CREATEDDATE,MODIFIEDBY,MODIFIEDDATE,ZIPCODE,STRINGFIELD7,STRINGFIELD8,STRINGFIELD9,STRINGFIELD10,STRINGFIELD11,STRINGFIELD12,STRINGFIELD13,STRINGFIELD14,STRINGFIELD15,STRINGFIELD16,STRINGFIELD17,STRINGFIELD18,STRINGFIELD19,STRINGFIELD20,OWNER,AREA_ID,ZIPCODE2,LASTCONTACTDATE,ADDRESS2,EVENT_GUID,EVENTDATE,DATATYPE,TEL_1_FLAG,TEL_2_FLAG,MOBILE_FLAG,OTHERS_FLAG,TEL_1_LASTCALLRESULT,TEL_2_LASTCALLRESULT,MOBILE_LASTCALLRESULT,OTHERS_LASTCALLRESULT,COMMENTS,CUSTOMERLEVEL,QQ,PARENT_GUID,TEL_SHIELD_FLAG,EMAIL_SHIELD_FLAG,FAX_SHIELD_FLAG,SMS_SHIELD_FLAG,ADDRESS_SHIELD_FLAG,BEOBJECTIVETYPE_ID,ISMAIN,POSITION,CUSTOMERNAMEABB,HOMEPAGE,DEPTDESC,INDUSTRYTYPE_ID,LOGINNAME,PASSWORD,PARTNER_GUID,SERVICEFROM,SERVICETO,CUSTOMERTYPE2_FLAG,PARTNER_RELATION_ID,PARENT_RELATION_ID,STRINGFIELD101,STRINGFIELD102,STRINGFIELD103,STRINGFIELD104,STRINGFIELD105,STRINGFIELD106,STRINGFIELD107,STRINGFIELD108,STRINGFIELD109,STRINGFIELD110,STRINGFIELD111,STRINGFIELD112,STRINGFIELD113,STRINGFIELD114,STRINGFIELD115,AREA,CITY,PROVINCE,MOBILENO,CUSTNAME,SALUTATION_ID,CREDENTVALIDPERIOD,WECHATID,AGE,LICENSEP,LICENSET,LICENSETDATE,APCALLPHONETYPE,ROLEID,WORKSTATUS,webchat_nickname,WEBCHAT_COMMENT,OPEN_ID,ICON_URL,WEBCHAT_RATING,WEBCHAT_TARGET_ID,WEBCHAT_RC_TOKEN,PAUSEDDATE,blacklist,customernamebb,tel_1_enc,tel_2_enc,mobile_enc,others_enc,fax_enc,address_enc,club_id_enc,apcallphone,webchat_rate,recommend,sq_1,sq_2,sq_3,sq_4,WeChat_Add_Status,Add_WeChat_date,Sq_5,Sq_6,Sq_7,sq_8,sq_9,sq_10,sq_11,sq_12,sq_13,sq_14,sq_15,is_transmission,registerstatus,stashtime,audittime,Invitee_count,depositstatus,depositUpdateTime,distributionstatus,registerChannelId,registerChannelName,registerAttribution,transferTime,transferChannelId,transferChannelName,transferAttribution,openingChannelId,openingChannelName,openingAttribution,firstAssetTime,firstAssetChannelId,firstAssetChannel,firstAssetAttribution,gwapply_status,workordertype_status,activity_status,chat_req_status,wechat_rec_status,lastchattime,ibontime,obontime,wecomtime,wecomaddviatime,latestbehaviortime,latestbehaviortype,CUSTOMERID_ENC,OPEN_ID_ENC,E_MAIL_ENC,STRINGFIELD2_ENC";
			String dataSourceType = eliteBean.getdatasourcename("dataSource");
			if(dataSourceType.contains("Oracle")){
				log.debug("-------------dbtype is: Oracle");
				now = "sysdate";
			}else if(dataSourceType.contains("Microsoft SQL")){
				log.debug("-------------dbtype is: Microsoft SQL");
 				now = "getdate()";
 				sqlType ="sqlServer";
			}else if(dataSourceType.contains("MySQL")){
			log.debug("---------------dbtype is: MySQL");
				now = "now()";
			}


    	if (mergeCustList.length() > 0 && mainCustGuid.length() > 0 ){//主客户和合并客户都存在
    		//取出合并客户guid
       		def mergeCustListArray = mergeCustList.split(",");
       		log.debug("mergeCustListArray:"+mergeCustListArray);
       		log.debug("mergeCustListArray.length:"+mergeCustListArray.length);
       		//insert主客户记录进his

        	def insetSqlCustomer = "INSERT INTO MERGECUSTOMER_HIS(" + custsqlfield +",MERGECUSTOMER_GUID,MERGEBY,MERGEDATE) select " + custsqlfield +",'"+mainCustGuid+"','"+operater+"',"+now+" from customer c where customer_guid = '"+mainCustGuid+"' and stringfield105='Esop'";
       		def insetSqlCustomerResult = eliteBean.update(insetSqlCustomer,'dataSource');
       		log.debug("insetSqlCustomerResult:"+insetSqlCustomerResult);


       		//补充模式:查询需要的补充字段
       		if("2".equals(mergePattern)){
       			def queryField = "";
       			def queryFieldResult= "";

       			try{//代码区
       				queryField = "select custfield from mergecustfield where status = 1";//查询需要补充的字段
       				queryFieldResult = eliteBean.chaxun_sql(queryField,'dataSource');
       				log.debug("queryFieldResult"+queryFieldResult);//得到需要补充的字段
       				log.debug("queryFieldResult.size()："+queryFieldResult.size());
       			}catch(Exception e){//异常处理
					log.debug("queryFieldResult is not exsit");
				}

       			if(queryFieldResult.size() > 0){//补充模式：如果存在需要补充的，则进行拼接成能进行sql查询的字符串
				def flString = "";
					for(def m=0;m<queryFieldResult.size();m++){
						Map fl =  queryFieldResult.get(m);
						log.debug("fl:" + fl);
						if(m == queryFieldResult.size() - 1){
						flString += fl.get("custfield");
						} else {
						flString += fl.get("custfield")+ ",";
						}
						log.debug("flString:" + flString);
					}
					//以下查询customer表里是否为空
					def qureyCust ="";
					def qureyCustResult = "";
					try{//代码区
						qureyCust = "select " + flString +" from customer where customer_guid = '"+mainCustGuid+"' and stringfield105='Esop'";
						qureyCustResult = eliteBean.chaxun_sql(qureyCust,'dataSource');
						log.debug("qureyCustResult："+qureyCustResult);
       				}catch(Exception e){//异常处理
						log.debug("qureyCustResult is not exsit");
					}

//					if(qureyCustResult.length >0 ){}
						def custList = qureyCustResult.get(0);
						log.debug("custList:"+custList);
						//String nullField = "";
						for(def key : custList.keySet()){
							log.debug("key:"+key);//custfield
							//if(StringUtils.isEmpty(custList.get(key))){
							//nullField += key+",";
							nullFieldList.add(key);
							//}
						}	
					//nullField = nullField.substring(0,nullField.length() - 1);
					log.debug("nullFieldList:"+nullFieldList);

					//得到补充的数据
		       		log.debug("customer表为空字段:"+nullFieldList);
		       		def nullFieldString ="";
		       		for(int i = 0; i < nullFieldList.size(); i++){
		       			if(i ==  nullFieldList.size()-1 ){
		       				nullFieldString += nullFieldList[i];
		       			}else{
		       				nullFieldString += nullFieldList[i] + ",";
		       			}
					}
					log.debug("nullFieldString:"+nullFieldString);
					
					def newestField = "";
					def newestFieldResult = "";
//					try{//代码区
						log.debug("--------sqlType:"+sqlType);
						if(sqlType == "sqlServer"){
							//newestField = "select "+nullFieldString +" from (select top 1 c.*,case when modifieddate is null then createddate else modifieddate end as dd from customer c  WHERE createddate is not null and customer_guid in ("+ mergeCustForSql +") order by dd desc ) addfield ";
		       				//ziva1106 不过滤createdate为空的数据
		       				newestField = "select "+nullFieldString +" from (select top 1 c.*,case when modifieddate is null then createddate else modifieddate end as dd from customer c  WHERE customer_guid in ("+ mergeCustForSql +") and stringfield105='Esop' order by dd desc ) addfield ";
		       				newestFieldResult =eliteBean.chaxun_sql(newestField,'dataSource');
		       				log.debug("sqlserver----更新数据newestFieldResult:"+newestFieldResult);
						}else{
							//newestField = "select "+nullFieldString +" from (select c.*,case when modifieddate is null then createddate else modifieddate end as dd from customer c  WHERE createddate is not null and customer_guid in ("+ mergeCustForSql +") order by dd desc ) addfield ";
							//ziva1106 不过滤createdate为空的数据
							newestField = "select "+nullFieldString +" from (select c.*,case when modifieddate is null then createddate else modifieddate end as dd from customer c  WHERE customer_guid in ("+ mergeCustForSql +") and stringfield105='Esop' order by dd desc ) addfield ";
		       				newestFieldResult =eliteBean.chaxun_sql(newestField,'dataSource');
		       				log.debug("----更新数据newestFieldResult:"+newestFieldResult);
						}
		       			
//		       		}catch(Exception e){//异常处理
//						log.debug("newestField is not exsit");
//					}
		       			
		       		//得到合并客户按modifieddate排序
		     		if(newestFieldResult.size()>0){
			       		def newestFieldResult1 = newestFieldResult.get(0);
			       		log.debug("newestFieldResult1:"+newestFieldResult1);
			       		//得到补充数据newestFieldResult1:[tel_1:111,address:ccc...]
			       		//补充主客户guid：mainCustGuid
			       		log.debug("newestFieldResult1.size():"+newestFieldResult1.size());
			       		//如果存在补充字段
		       			if(newestFieldResult1.size()>0){
		       				//循环补充进customer
							for(def key : newestFieldResult1.keySet()){
								log.debug("key:"+key); 
								def value = newestFieldResult1.get(key);
								log.debug("value:"+value);

								try{//代码区
									def addSql ="update customer set "+ key +" = '"+ value +"' where customer_guid = '"+ mainCustGuid +"' and stringfield105='Esop'";
									def addSqlResult = eliteBean.update(addSql,'dataSource');
									log.debug("addSqlResult:"+addSqlResult);
								}catch(Exception e){//异常处理
									log.debug("addSqlResult is not exsit");
								}


							}//结束：补充字段>0个
		       			}//结束：存在补充字段
					}//结束：合并客户按modifieddate排序

 				}//补充模式结束：得到真正需要更新补充的字段
       		}//补充模式=2结束


       		//循环array取出合并客户guid
       		for(def i=0;i < mergeCustListArray.length;i++){
				//1进行insert入his表
				def mergeCust = mergeCustListArray[i];
	       		def insetSqls = "INSERT INTO MERGECUSTOMER_HIS(" + custsqlfield +",MERGECUSTOMER_GUID,MERGEBY,MERGEDATE) select " + custsqlfield +",'"+mainCustGuid+"','"+operater+"',"+now+" from customer c where customer_guid = '"+mergeCust+"' and stringfield105='Esop'";
	       		def insetSqlsResult = eliteBean.update(insetSqls,'dataSource');
		       	log.debug("insetSqlsResult:"+insetSqlsResult);

				def querySqlcmc = "";
				def cmcList ="";
				try{//代码区
					//2c_mergecustomer取出要更新表和字段名，把主客户的guid更新上去
	  	 			querySqlcmc = "select tablename,columnname from c_mergecustomer";
					cmcList = eliteBean.chaxun_sql(querySqlcmc,'dataSource');
					log.debug("cmcList:"+cmcList);
					log.debug("cmcList.size():"+cmcList.size());
				}catch(Exception e){//异常处理
					log.debug("cmcList is not exsit");
				}

				if(cmcList.size()>0){
					for(def n=0;n<cmcList.size();n++){
				        try{//代码区
							def updateSqlcmc = "update " + (String)cmcList.get(n).get("tablename") + " set " + (String)cmcList.get(n).get("columnname") + " ='"+mainCustGuid+"' where "+(String)cmcList.get(n).get("columnname")+" = '"+mergeCust+"'";
 							def updateSqlcmcResult = eliteBean.update(updateSqlcmc,'dataSource');
							log.debug("updateSqlcmcResult:"+updateSqlcmcResult);
						}catch(Exception e){//异常处理
							log.debug("updateSqlcmcResult is not exsit");
						}
 					}
				}

				//3delete customer表合并客户
		 		def deletesql = "delete from customer where customer_guid = '"+mergeCust+"' and stringfield105='Esop'";
				def deteteSqlResult = eliteBean.update(deletesql,'dataSource');
				log.debug("deteteSqlResult:"+deteteSqlResult);
				//return 100;
			}//结束循环合并客户guid
				return 100;

		}//主客户和合并客户都存在执行逻辑结束
	
	}
}
